/****** Object:  Stored Procedure [dbo].spPhieuGiaoHangInsert Script Date: 6/12/2012 ******/
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[spPhieuGiaoHangInsert]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
	DROP PROCEDURE [dbo].[spPhieuGiaoHangInsert]
GO

/******************************************************************************
**		Change History
*******************************************************************************
**		Date:		Author:					Description:
**		--------	--------				-----------------------------------
**		21/06/2012	SongTM					Innit created
*******************************************************************************/

CREATE PROCEDURE [dbo].[spPhieuGiaoHangInsert]
(
	@SoPG nvarchar(14)Output,
	@NgayGiao date,
	@PhuongThucGiao nvarchar(35),
	@PhuongThucTT nvarchar(35),
	@NguoiGiao nvarchar(35),
	@NguoiNhan nvarchar(35),
	@SoHD nvarchar(12),
	@GhiChu nvarchar(200),
	@MaKH nvarchar(6),
	@SoPX nvarchar(14)
)
WITH ENCRYPTION

AS

SET NOCOUNT ON

	Declare @Stt char(3)
	Declare @y varchar(2)
	Declare @m varchar(2)

	--set @y = RIGHT(YEAR(@NgayGiao),2)
	set @y = RIGHT('0' + CAST(YEAR(@NgayGiao) as varchar(2)), 2)
	
	--set @m =RIGHT(MONTH(@NgayGiao),2)
	set @m = RIGHT('0' + CAST(MONTH(@NgayGiao) as varchar(2)), 2)
		
	if not exists (select * from [tbl_PhieuGiaoHang])
		Set @Stt=1
	else if not exists (select * from tbl_PhieuGiaoHang 
						where RIGHT(YEAR(NgayGiao),2) = @y and RIGHT(MONTH(NgayGiao),2) = @m )
		Set @Stt=1
	else
		Set @Stt=(select MAX(RIGHT(LEFT(SoPG,5),3)) from tbl_PhieuGiaoHang 
		where RIGHT(YEAR(NgayGiao),2) = @y and RIGHT(MONTH(NgayGiao),2) = @m )+1
	
	Set @Stt=REPLICATE('0',3-LEN(@Stt))+@Stt
	set @m = REPLICATE('0',2-LEN(@m))+@m
	Set @SoPG='PGxxx/mmyy/NP'
	Set @SoPG=REPLACE(@SoPG,'xxx',@Stt);
	Set @SoPG=REPLACE(@SoPG,'mm',@m);
	Set @SoPG=REPLACE(@SoPG,'yy',@y);

INSERT INTO [tbl_PhieuGiaoHang]
(
	[SoPG],
	[NgayGiao],
	[PhuongThucGiao],
	[PhuongThucTT],
	[NguoiGiao],
	[NguoiNhan],
	[SoHD],
	[GhiChu],
	[MaKH],
	[SoPX]
)
VALUES
(
	@SoPG,
	@NgayGiao,
	@PhuongThucGiao,
	@PhuongThucTT,
	@NguoiGiao,
	@NguoiNhan,
	@SoHD,
	@GhiChu,
	@MaKH,
	@SoPX
)
GO

--GRANT EXECUTE ON [dbo].[spPhieuGiaoHangInsert] TO [sa]
--GO
